package com.code.generator.sqlgenerator.initexcel;

import com.code.config.source.DatasourceFactory;
import com.code.entity.generator.Column;
import com.code.entity.generator.Table;
import org.apache.commons.lang3.StringUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ExcelMetadataProvider {

    private static String getDDLSql = "select table_name,column_name,data_type,character_maximum_length,column_comment,numeric_precision,"
            + "numeric_scale,case is_nullable when 'no' then '是' else '' end is_notnull,column_type,"
            + "'1.0' version from information_schema.columns where table_schema = 'ai_gateway' and table_name = '%s'";
    private static String getTablesSql = "select table_name from information_schema.tables where table_schema='ai_gateway'";
    private static String getIndexsSql = "show index from %s where key_name <> 'PRIMARY'";

    private static List<String> getTables() {
        List<String> tableNames = new ArrayList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        try {
            DataSource dataSource = DatasourceFactory.getDataSource();
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(getTablesSql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                tableNames.add(resultSet.getString("table_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tableNames;
    }

    public static List<Table> getTableColumns() {
        List<String> tableNames = getTables();

        List<Table> tables = new ArrayList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        ResultSet indexResultSet = null;
        PreparedStatement statement = null;
        PreparedStatement indexStatement = null;
        try {
            DataSource dataSource = DatasourceFactory.getDataSource();
            connection = dataSource.getConnection();
            for (String table : tableNames) {
                if (table.endsWith("_his") || table.startsWith("act_")) {
                    continue;
                }
                Table model = new Table();
                model.setTableName(table);
                List<Column> tableColumns = new ArrayList<>();

                String sql = String.format(getDDLSql, table);
                statement = connection.prepareStatement(sql);
                resultSet = statement.executeQuery();

                String indexSql = String.format(getIndexsSql, table);
                indexStatement = connection.prepareStatement(indexSql);
                indexResultSet = indexStatement.executeQuery();
                List<String> indexColumns = new ArrayList<>();
                while (indexResultSet.next()) {
                    indexColumns.add(indexResultSet.getString("Column_name").toLowerCase());
                }
                while (resultSet.next()) {
                    Column column = new Column();
                    column.setTableName(table);
                    String columnName = resultSet.getString("column_name");
                    column.setColumnName(columnName);
                    column.setDataType(resultSet.getString("data_type"));
                    String length = resultSet.getString("column_type");
                    if (StringUtils.isNotBlank(length) && length.indexOf("(") > 0) {
                        column.setDataLength(length.substring(length.indexOf("(") + 1, length.length() - 1));
                    }
                    column.setColumnComment(resultSet.getString("column_comment"));
                    column.setNullState(resultSet.getString("is_notnull"));
                    column.setVersion(resultSet.getString("version"));
                    if (indexColumns.contains(columnName.toLowerCase())) {
                        column.setIndex("是");
                    }
                    if (tableNames.contains(table + "_his")) {
                        column.setWithHistory("是");
                    }
                    tableColumns.add(column);
                }
                model.setColumns(tableColumns);
                tables.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (indexResultSet != null) {
                    indexResultSet.close();
                }
                if (indexStatement != null) {
                    indexStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tables;
    }

}
